﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;

namespace Sohar.Core
{
	public class SoharGateway
	{
		private static DbProviderFactory provider = DbProviderFactories.GetFactory("System.Data.SqlClient");

		static SoharGateway()
		{
			provider = DbProviderFactories.GetFactory("System.Data.SqlClient");
		}

		private DbConnection GetOpenConnection()
		{
			DbConnection connection = provider.CreateConnection();
			connection.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=EnglishLearning;Integrated Security=SSPI";
			connection.Open();

			return connection;
		}

		private DbCommand GetCommand(string text)
		{
			DbCommand command = provider.CreateCommand();
			command.CommandText = text;
			return command;
		}

		private DbParameter AddParameter(DbCommand command, string name, object value)
		{
			DbParameter p = command.CreateParameter();
			p.ParameterName = name;
			p.Value = value;
			command.Parameters.Add(p);
			return p;
		}

		private int ExecuteNonQuery(DbCommand command)
		{
			command.Connection = GetOpenConnection();
			return command.ExecuteNonQuery();
		}

		private int ExecuteNonQuery(string text)
		{
			DbCommand command = GetCommand(text);
			return ExecuteNonQuery(command);
		}

		private object ExecuteScalar(DbCommand command)
		{
			command.Connection = GetOpenConnection();
			return command.ExecuteScalar();
		}

		private object ExecuteScalar(string text)
		{
			DbCommand command = GetCommand(text);
			return ExecuteScalar(command);
		}

		private DbDataReader ExecuteReader(DbCommand command)
		{
			command.Connection = GetOpenConnection();
			return command.ExecuteReader();
		}

		private DbDataReader ExecuteReader(string text)
		{
			DbCommand command = GetCommand(text);
			return ExecuteReader(command);
		}

		public int InsertWord(string word)
		{
			DbCommand command = GetCommand(@"insert into Word (Value) values (@value); select scope_identity()");
			AddParameter(command, "@value", word);
			return (int)(decimal)ExecuteScalar(command);
		}

		public int InsertMeaning(string meaning, int wordID, int classID)
		{
			DbCommand command = GetCommand(@"insert into Meaning (Value, WordID, ClassID) values (@value, @wordID, @classID); select scope_identity()");
			AddParameter(command, "@value", meaning);
			AddParameter(command, "@wordID", wordID);
			AddParameter(command, "@classID", classID);
			return (int)(decimal)ExecuteScalar(command);
		}

		public DbDataReader GetWord(string wordPart)
		{
			DbCommand command = GetCommand(@"select ID, Value from Word where Value like '%' + @value + '%'");
			AddParameter(command, "@value", wordPart ?? string.Empty);
			return ExecuteReader(command);
		}

		public DbDataReader GetMeaningByWord(int wordID)
		{
			DbCommand command = GetCommand(@"select ID, Value, ClassID, Example from Meaning where WordID = @wordID");
			AddParameter(command, "@wordID", wordID);
			return ExecuteReader(command);
		}

		public IDataReader GetAllClasses()
		{
			return ExecuteReader(@"select ID, Name, Abbreviation from Class");
		}
	}
}
